<!DOCTYPE html>
<html lang="en-US">
  
<!-- Mirrored from heavy_code_industry.gitee.io/code_heavy_industry/pro018-MySQL-high/lecture/chapter06/index.html by HTTrack Website Copier/3.x [XR&CO'2014], Tue, 10 May 2022 05:13:08 GMT -->
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>代码重工</title>
    <meta name="generator" content="VuePress 1.9.5">
    
    <meta name="description" content="代码重工">
    
    <link rel="preload" href="../../../assets/css/0.styles.f7de6701.css" as="style"><link rel="preload" href="../../../assets/js/app.972bd0f0.js" as="script"><link rel="preload" href="../../../assets/js/4.904e76b5.js" as="script"><link rel="preload" href="../../../assets/js/25.a5f2f737.js" as="script"><link rel="prefetch" href="../../../assets/js/1.d66e04b2.js"><link rel="prefetch" href="../../../assets/js/10.7093d550.js"><link rel="prefetch" href="../../../assets/js/100.384331b2.js"><link rel="prefetch" href="../../../assets/js/101.582f78e7.js"><link rel="prefetch" href="../../../assets/js/102.17f2c0ec.js"><link rel="prefetch" href="../../../assets/js/103.a530857d.js"><link rel="prefetch" href="../../../assets/js/104.62821ce1.js"><link rel="prefetch" href="../../../assets/js/105.11cdcc82.js"><link rel="prefetch" href="../../../assets/js/106.d57c1e75.js"><link rel="prefetch" href="../../../assets/js/107.7ebeb306.js"><link rel="prefetch" href="../../../assets/js/108.c067b47b.js"><link rel="prefetch" href="../../../assets/js/109.9495d2ca.js"><link rel="prefetch" href="../../../assets/js/11.acc2935e.js"><link rel="prefetch" href="../../../assets/js/110.7f58dbd3.js"><link rel="prefetch" href="../../../assets/js/111.f78239ff.js"><link rel="prefetch" href="../../../assets/js/112.8eb72445.js"><link rel="prefetch" href="../../../assets/js/113.3f5aff71.js"><link rel="prefetch" href="../../../assets/js/114.9f65cf57.js"><link rel="prefetch" href="../../../assets/js/115.dcaea004.js"><link rel="prefetch" href="../../../assets/js/116.726c5aac.js"><link rel="prefetch" href="../../../assets/js/117.54d6c26a.js"><link rel="prefetch" href="../../../assets/js/118.3f4e0bda.js"><link rel="prefetch" href="../../../assets/js/119.95ed5dc6.js"><link rel="prefetch" href="../../../assets/js/12.a5354f50.js"><link rel="prefetch" href="../../../assets/js/120.4fd08fac.js"><link rel="prefetch" href="../../../assets/js/121.f4aadbe5.js"><link rel="prefetch" href="../../../assets/js/122.d588a1e0.js"><link rel="prefetch" href="../../../assets/js/123.781e7192.js"><link rel="prefetch" href="../../../assets/js/124.ed312291.js"><link rel="prefetch" href="../../../assets/js/125.bceffbb9.js"><link rel="prefetch" href="../../../assets/js/126.51bf552f.js"><link rel="prefetch" href="../../../assets/js/127.4800687b.js"><link rel="prefetch" href="../../../assets/js/128.fcdd6f2a.js"><link rel="prefetch" href="../../../assets/js/129.2a25d78a.js"><link rel="prefetch" href="../../../assets/js/13.88a0c565.js"><link rel="prefetch" href="../../../assets/js/130.dcfe7623.js"><link rel="prefetch" href="../../../assets/js/131.c29e7c45.js"><link rel="prefetch" href="../../../assets/js/132.9a412a4b.js"><link rel="prefetch" href="../../../assets/js/133.615218d5.js"><link rel="prefetch" href="../../../assets/js/134.2168ca5a.js"><link rel="prefetch" href="../../../assets/js/135.a5e262ff.js"><link rel="prefetch" href="../../../assets/js/136.7dba5235.js"><link rel="prefetch" href="../../../assets/js/137.445576e7.js"><link rel="prefetch" href="../../../assets/js/138.577b3f66.js"><link rel="prefetch" href="../../../assets/js/139.b646f1ac.js"><link rel="prefetch" href="../../../assets/js/14.36391765.js"><link rel="prefetch" href="../../../assets/js/140.52abf81e.js"><link rel="prefetch" href="../../../assets/js/141.807c626b.js"><link rel="prefetch" href="../../../assets/js/142.adf9e127.js"><link rel="prefetch" href="../../../assets/js/143.9773d979.js"><link rel="prefetch" href="../../../assets/js/144.5658ae76.js"><link rel="prefetch" href="../../../assets/js/145.2d2fb13b.js"><link rel="prefetch" href="../../../assets/js/146.0e322019.js"><link rel="prefetch" href="../../../assets/js/147.50b34ac1.js"><link rel="prefetch" href="../../../assets/js/148.463aab7c.js"><link rel="prefetch" href="../../../assets/js/149.9bd7c539.js"><link rel="prefetch" href="../../../assets/js/15.4a8e9d28.js"><link rel="prefetch" href="../../../assets/js/150.9449ca28.js"><link rel="prefetch" href="../../../assets/js/151.1961c6db.js"><link rel="prefetch" href="../../../assets/js/152.18709656.js"><link rel="prefetch" href="../../../assets/js/153.a4bf78f6.js"><link rel="prefetch" href="../../../assets/js/154.6c90b1d5.js"><link rel="prefetch" href="../../../assets/js/155.024247e2.js"><link rel="prefetch" href="../../../assets/js/156.76281b57.js"><link rel="prefetch" href="../../../assets/js/157.a7c02212.js"><link rel="prefetch" href="../../../assets/js/158.0d00acef.js"><link rel="prefetch" href="../../../assets/js/159.9180c937.js"><link rel="prefetch" href="../../../assets/js/16.87793b5d.js"><link rel="prefetch" href="../../../assets/js/160.d7a98288.js"><link rel="prefetch" href="../../../assets/js/161.389ddf84.js"><link rel="prefetch" href="../../../assets/js/162.ab22d7dc.js"><link rel="prefetch" href="../../../assets/js/163.087257c6.js"><link rel="prefetch" href="../../../assets/js/164.addfc5af.js"><link rel="prefetch" href="../../../assets/js/165.f90e6b2a.js"><link rel="prefetch" href="../../../assets/js/166.fa06e4b6.js"><link rel="prefetch" href="../../../assets/js/167.a00e722b.js"><link rel="prefetch" href="../../../assets/js/168.e3855223.js"><link rel="prefetch" href="../../../assets/js/169.e0c63a4e.js"><link rel="prefetch" href="../../../assets/js/17.dec86310.js"><link rel="prefetch" href="../../../assets/js/170.7c5835f4.js"><link rel="prefetch" href="../../../assets/js/171.7d9ae110.js"><link rel="prefetch" href="../../../assets/js/172.4da5caea.js"><link rel="prefetch" href="../../../assets/js/173.f8ac6155.js"><link rel="prefetch" href="../../../assets/js/174.3b831765.js"><link rel="prefetch" href="../../../assets/js/175.01782f3d.js"><link rel="prefetch" href="../../../assets/js/176.6eab6644.js"><link rel="prefetch" href="../../../assets/js/177.86339767.js"><link rel="prefetch" href="../../../assets/js/178.c437c804.js"><link rel="prefetch" href="../../../assets/js/179.927227c9.js"><link rel="prefetch" href="../../../assets/js/18.9167733d.js"><link rel="prefetch" href="../../../assets/js/180.caa2c660.js"><link rel="prefetch" href="../../../assets/js/181.c186b8ac.js"><link rel="prefetch" href="../../../assets/js/182.0475844f.js"><link rel="prefetch" href="../../../assets/js/183.2c5950c8.js"><link rel="prefetch" href="../../../assets/js/184.82b336f6.js"><link rel="prefetch" href="../../../assets/js/185.e2d4a46e.js"><link rel="prefetch" href="../../../assets/js/186.99a3de8a.js"><link rel="prefetch" href="../../../assets/js/187.d557500c.js"><link rel="prefetch" href="../../../assets/js/188.e81bd251.js"><link rel="prefetch" href="../../../assets/js/189.d07d368a.js"><link rel="prefetch" href="../../../assets/js/19.017b4ad0.js"><link rel="prefetch" href="../../../assets/js/190.173606ea.js"><link rel="prefetch" href="../../../assets/js/191.c5ac0c71.js"><link rel="prefetch" href="../../../assets/js/192.127d6d93.js"><link rel="prefetch" href="../../../assets/js/193.2f9011c9.js"><link rel="prefetch" href="../../../assets/js/194.14f534a3.js"><link rel="prefetch" href="../../../assets/js/195.65939036.js"><link rel="prefetch" href="../../../assets/js/196.6e3cf025.js"><link rel="prefetch" href="../../../assets/js/197.4d18b1d7.js"><link rel="prefetch" href="../../../assets/js/198.c2e521c3.js"><link rel="prefetch" href="../../../assets/js/199.d72c63dd.js"><link rel="prefetch" href="../../../assets/js/20.6aa686d0.js"><link rel="prefetch" href="../../../assets/js/200.56d7dbd3.js"><link rel="prefetch" href="../../../assets/js/201.c72a395d.js"><link rel="prefetch" href="../../../assets/js/202.cb32f7af.js"><link rel="prefetch" href="../../../assets/js/203.285e6b92.js"><link rel="prefetch" href="../../../assets/js/204.ead50027.js"><link rel="prefetch" href="../../../assets/js/205.190c1baf.js"><link rel="prefetch" href="../../../assets/js/206.4dab4ba0.js"><link rel="prefetch" href="../../../assets/js/207.f11eee48.js"><link rel="prefetch" href="../../../assets/js/208.e2f8ca82.js"><link rel="prefetch" href="../../../assets/js/209.e7e74aff.js"><link rel="prefetch" href="../../../assets/js/21.f7d66fe8.js"><link rel="prefetch" href="../../../assets/js/210.69c4a0ed.js"><link rel="prefetch" href="../../../assets/js/211.a1ab5062.js"><link rel="prefetch" href="../../../assets/js/212.6d0473bd.js"><link rel="prefetch" href="../../../assets/js/213.bd120419.js"><link rel="prefetch" href="../../../assets/js/214.c7d4362c.js"><link rel="prefetch" href="../../../assets/js/215.8adc34dc.js"><link rel="prefetch" href="../../../assets/js/216.abf8ff23.js"><link rel="prefetch" href="../../../assets/js/217.10f9fc46.js"><link rel="prefetch" href="../../../assets/js/218.52e31fec.js"><link rel="prefetch" href="../../../assets/js/219.ea0551af.js"><link rel="prefetch" href="../../../assets/js/22.b1de9b18.js"><link rel="prefetch" href="../../../assets/js/220.6be520c8.js"><link rel="prefetch" href="../../../assets/js/221.c0d93576.js"><link rel="prefetch" href="../../../assets/js/222.1bb0eaff.js"><link rel="prefetch" href="../../../assets/js/223.8492b065.js"><link rel="prefetch" href="../../../assets/js/224.5897434e.js"><link rel="prefetch" href="../../../assets/js/225.51902428.js"><link rel="prefetch" href="../../../assets/js/226.9da70eee.js"><link rel="prefetch" href="../../../assets/js/227.9e113839.js"><link rel="prefetch" href="../../../assets/js/228.d689b27d.js"><link rel="prefetch" href="../../../assets/js/229.28f74090.js"><link rel="prefetch" href="../../../assets/js/23.e3b58d3d.js"><link rel="prefetch" href="../../../assets/js/230.465a35a0.js"><link rel="prefetch" href="../../../assets/js/231.219aeb63.js"><link rel="prefetch" href="../../../assets/js/232.84035c50.js"><link rel="prefetch" href="../../../assets/js/233.ef27424d.js"><link rel="prefetch" href="../../../assets/js/234.5dcf1349.js"><link rel="prefetch" href="../../../assets/js/235.bcc0c537.js"><link rel="prefetch" href="../../../assets/js/236.9bb64465.js"><link rel="prefetch" href="../../../assets/js/237.f3300514.js"><link rel="prefetch" href="../../../assets/js/238.ca7af9ac.js"><link rel="prefetch" href="../../../assets/js/239.fdfe2127.js"><link rel="prefetch" href="../../../assets/js/24.2e010582.js"><link rel="prefetch" href="../../../assets/js/240.8a4488af.js"><link rel="prefetch" href="../../../assets/js/241.9987dfab.js"><link rel="prefetch" href="../../../assets/js/242.819ed984.js"><link rel="prefetch" href="../../../assets/js/243.bf129ecd.js"><link rel="prefetch" href="../../../assets/js/244.030b4b49.js"><link rel="prefetch" href="../../../assets/js/245.7663f620.js"><link rel="prefetch" href="../../../assets/js/246.ed2b2fc0.js"><link rel="prefetch" href="../../../assets/js/247.54dd84f1.js"><link rel="prefetch" href="../../../assets/js/248.a1c69038.js"><link rel="prefetch" href="../../../assets/js/249.47dd7b3b.js"><link rel="prefetch" href="../../../assets/js/250.ec8017a5.js"><link rel="prefetch" href="../../../assets/js/251.89ab0cf7.js"><link rel="prefetch" href="../../../assets/js/252.5ec1eea4.js"><link rel="prefetch" href="../../../assets/js/253.31a37280.js"><link rel="prefetch" href="../../../assets/js/254.fcfabb55.js"><link rel="prefetch" href="../../../assets/js/255.286f4c17.js"><link rel="prefetch" href="../../../assets/js/256.ce77595b.js"><link rel="prefetch" href="../../../assets/js/257.cb3677c0.js"><link rel="prefetch" href="../../../assets/js/258.ca18e0e9.js"><link rel="prefetch" href="../../../assets/js/259.65dc8492.js"><link rel="prefetch" href="../../../assets/js/26.cce346fc.js"><link rel="prefetch" href="../../../assets/js/260.475ddaba.js"><link rel="prefetch" href="../../../assets/js/261.643b784a.js"><link rel="prefetch" href="../../../assets/js/262.e5092c67.js"><link rel="prefetch" href="../../../assets/js/263.5e85d32e.js"><link rel="prefetch" href="../../../assets/js/264.6ff4b789.js"><link rel="prefetch" href="../../../assets/js/265.fee7416a.js"><link rel="prefetch" href="../../../assets/js/266.8d8a5e44.js"><link rel="prefetch" href="../../../assets/js/267.8c62a655.js"><link rel="prefetch" href="../../../assets/js/268.762073ff.js"><link rel="prefetch" href="../../../assets/js/269.ffe69921.js"><link rel="prefetch" href="../../../assets/js/27.22790cda.js"><link rel="prefetch" href="../../../assets/js/270.d5bf783f.js"><link rel="prefetch" href="../../../assets/js/271.83fd2f70.js"><link rel="prefetch" href="../../../assets/js/272.6de67fa9.js"><link rel="prefetch" href="../../../assets/js/273.628e1b77.js"><link rel="prefetch" href="../../../assets/js/274.7f0d82c6.js"><link rel="prefetch" href="../../../assets/js/275.d4478098.js"><link rel="prefetch" href="../../../assets/js/276.56649e5b.js"><link rel="prefetch" href="../../../assets/js/277.05b881f2.js"><link rel="prefetch" href="../../../assets/js/278.c079de28.js"><link rel="prefetch" href="../../../assets/js/279.58101972.js"><link rel="prefetch" href="../../../assets/js/28.ac0ba3fa.js"><link rel="prefetch" href="../../../assets/js/280.5e32c770.js"><link rel="prefetch" href="../../../assets/js/281.59fb6cf4.js"><link rel="prefetch" href="../../../assets/js/282.f22da512.js"><link rel="prefetch" href="../../../assets/js/283.db103d63.js"><link rel="prefetch" href="../../../assets/js/284.a373c7e0.js"><link rel="prefetch" href="../../../assets/js/285.55c6f720.js"><link rel="prefetch" href="../../../assets/js/286.f924c38c.js"><link rel="prefetch" href="../../../assets/js/287.52a35525.js"><link rel="prefetch" href="../../../assets/js/288.dcc32270.js"><link rel="prefetch" href="../../../assets/js/289.835ad2b5.js"><link rel="prefetch" href="../../../assets/js/29.6e1dc238.js"><link rel="prefetch" href="../../../assets/js/290.749e38a9.js"><link rel="prefetch" href="../../../assets/js/291.90916822.js"><link rel="prefetch" href="../../../assets/js/292.b453b275.js"><link rel="prefetch" href="../../../assets/js/293.11ad0754.js"><link rel="prefetch" href="../../../assets/js/294.dddc45b9.js"><link rel="prefetch" href="../../../assets/js/295.74ecca38.js"><link rel="prefetch" href="../../../assets/js/296.2061efa6.js"><link rel="prefetch" href="../../../assets/js/297.b838fcd5.js"><link rel="prefetch" href="../../../assets/js/298.8c08be1f.js"><link rel="prefetch" href="../../../assets/js/299.5a99490b.js"><link rel="prefetch" href="../../../assets/js/3.9ca9fb75.js"><link rel="prefetch" href="../../../assets/js/30.64f9cad0.js"><link rel="prefetch" href="../../../assets/js/300.a5010762.js"><link rel="prefetch" href="../../../assets/js/301.a2f8795a.js"><link rel="prefetch" href="../../../assets/js/302.129bdff3.js"><link rel="prefetch" href="../../../assets/js/303.57268b2c.js"><link rel="prefetch" href="../../../assets/js/304.5556b94f.js"><link rel="prefetch" href="../../../assets/js/305.72bbf9b8.js"><link rel="prefetch" href="../../../assets/js/306.821163a7.js"><link rel="prefetch" href="../../../assets/js/307.b85ceac7.js"><link rel="prefetch" href="../../../assets/js/308.7f794216.js"><link rel="prefetch" href="../../../assets/js/309.212b7a1d.js"><link rel="prefetch" href="../../../assets/js/31.386297cc.js"><link rel="prefetch" href="../../../assets/js/310.089b4a08.js"><link rel="prefetch" href="../../../assets/js/311.c7f6aabd.js"><link rel="prefetch" href="../../../assets/js/312.810271c0.js"><link rel="prefetch" href="../../../assets/js/313.030424f0.js"><link rel="prefetch" href="../../../assets/js/314.a95451b6.js"><link rel="prefetch" href="../../../assets/js/315.f883b88e.js"><link rel="prefetch" href="../../../assets/js/316.676ed945.js"><link rel="prefetch" href="../../../assets/js/317.0dcbeaef.js"><link rel="prefetch" href="../../../assets/js/318.bd172af6.js"><link rel="prefetch" href="../../../assets/js/319.e2d01ca1.js"><link rel="prefetch" href="../../../assets/js/32.8927ba28.js"><link rel="prefetch" href="../../../assets/js/320.09b01482.js"><link rel="prefetch" href="../../../assets/js/321.3b70dce2.js"><link rel="prefetch" href="../../../assets/js/322.5f7046ad.js"><link rel="prefetch" href="../../../assets/js/323.ffd56783.js"><link rel="prefetch" href="../../../assets/js/324.cfe0b188.js"><link rel="prefetch" href="../../../assets/js/325.cc2f1112.js"><link rel="prefetch" href="../../../assets/js/326.635a861b.js"><link rel="prefetch" href="../../../assets/js/327.94eacc0e.js"><link rel="prefetch" href="../../../assets/js/328.7d4790fb.js"><link rel="prefetch" href="../../../assets/js/329.38f7ef56.js"><link rel="prefetch" href="../../../assets/js/33.bd1f28e6.js"><link rel="prefetch" href="../../../assets/js/330.269edfe2.js"><link rel="prefetch" href="../../../assets/js/331.21623a36.js"><link rel="prefetch" href="../../../assets/js/332.1f1c659c.js"><link rel="prefetch" href="../../../assets/js/333.fe34384d.js"><link rel="prefetch" href="../../../assets/js/334.34a514a7.js"><link rel="prefetch" href="../../../assets/js/335.9272b0cc.js"><link rel="prefetch" href="../../../assets/js/336.796dcaf9.js"><link rel="prefetch" href="../../../assets/js/337.bfd29430.js"><link rel="prefetch" href="../../../assets/js/338.fdb020fd.js"><link rel="prefetch" href="../../../assets/js/339.d7f19890.js"><link rel="prefetch" href="../../../assets/js/34.36a2460a.js"><link rel="prefetch" href="../../../assets/js/340.b22b3f52.js"><link rel="prefetch" href="../../../assets/js/341.47e1d3f4.js"><link rel="prefetch" href="../../../assets/js/342.38ca17d1.js"><link rel="prefetch" href="../../../assets/js/343.2e7874e5.js"><link rel="prefetch" href="../../../assets/js/344.6697aea1.js"><link rel="prefetch" href="../../../assets/js/345.ad3be23a.js"><link rel="prefetch" href="../../../assets/js/346.d40fa749.js"><link rel="prefetch" href="../../../assets/js/347.881934aa.js"><link rel="prefetch" href="../../../assets/js/348.2e8d29b9.js"><link rel="prefetch" href="../../../assets/js/349.87f51cb4.js"><link rel="prefetch" href="../../../assets/js/35.80f36815.js"><link rel="prefetch" href="../../../assets/js/350.06081b68.js"><link rel="prefetch" href="../../../assets/js/351.f883c9ae.js"><link rel="prefetch" href="../../../assets/js/352.8fcd0728.js"><link rel="prefetch" href="../../../assets/js/353.2dd787b0.js"><link rel="prefetch" href="../../../assets/js/354.689db664.js"><link rel="prefetch" href="../../../assets/js/355.b9a083ea.js"><link rel="prefetch" href="../../../assets/js/356.3eaa4bf3.js"><link rel="prefetch" href="../../../assets/js/357.2f05ac11.js"><link rel="prefetch" href="../../../assets/js/358.c6777610.js"><link rel="prefetch" href="../../../assets/js/359.2ae0c8ac.js"><link rel="prefetch" href="../../../assets/js/36.f9bba134.js"><link rel="prefetch" href="../../../assets/js/360.c9811603.js"><link rel="prefetch" href="../../../assets/js/361.5c3068d4.js"><link rel="prefetch" href="../../../assets/js/362.a80ddcc6.js"><link rel="prefetch" href="../../../assets/js/363.ccbb8d6e.js"><link rel="prefetch" href="../../../assets/js/364.8da5de40.js"><link rel="prefetch" href="../../../assets/js/365.0d9f6c8a.js"><link rel="prefetch" href="../../../assets/js/366.e410e582.js"><link rel="prefetch" href="../../../assets/js/367.f8689b17.js"><link rel="prefetch" href="../../../assets/js/368.57119763.js"><link rel="prefetch" href="../../../assets/js/369.f92c7aed.js"><link rel="prefetch" href="../../../assets/js/37.8935c848.js"><link rel="prefetch" href="../../../assets/js/370.8e896c3a.js"><link rel="prefetch" href="../../../assets/js/371.e2565759.js"><link rel="prefetch" href="../../../assets/js/372.0028b0ce.js"><link rel="prefetch" href="../../../assets/js/373.82bfe997.js"><link rel="prefetch" href="../../../assets/js/374.434f814f.js"><link rel="prefetch" href="../../../assets/js/375.8478c681.js"><link rel="prefetch" href="../../../assets/js/376.998ce2de.js"><link rel="prefetch" href="../../../assets/js/377.670e9be5.js"><link rel="prefetch" href="../../../assets/js/378.c9cfc01b.js"><link rel="prefetch" href="../../../assets/js/379.0aacc719.js"><link rel="prefetch" href="../../../assets/js/38.86fba1a7.js"><link rel="prefetch" href="../../../assets/js/380.14a91411.js"><link rel="prefetch" href="../../../assets/js/381.9e783c93.js"><link rel="prefetch" href="../../../assets/js/382.cf2ffc03.js"><link rel="prefetch" href="../../../assets/js/383.c32b0740.js"><link rel="prefetch" href="../../../assets/js/384.4280025f.js"><link rel="prefetch" href="../../../assets/js/385.a816d541.js"><link rel="prefetch" href="../../../assets/js/386.1d8fc395.js"><link rel="prefetch" href="../../../assets/js/387.bc22626f.js"><link rel="prefetch" href="../../../assets/js/388.1cf59352.js"><link rel="prefetch" href="../../../assets/js/389.a09795bf.js"><link rel="prefetch" href="../../../assets/js/39.c8c0ff8e.js"><link rel="prefetch" href="../../../assets/js/390.6a223692.js"><link rel="prefetch" href="../../../assets/js/391.733d45f0.js"><link rel="prefetch" href="../../../assets/js/392.d76fd3df.js"><link rel="prefetch" href="../../../assets/js/393.c885df90.js"><link rel="prefetch" href="../../../assets/js/394.c344d389.js"><link rel="prefetch" href="../../../assets/js/395.687cbd0a.js"><link rel="prefetch" href="../../../assets/js/396.57e0df54.js"><link rel="prefetch" href="../../../assets/js/397.1a31ad36.js"><link rel="prefetch" href="../../../assets/js/398.59535325.js"><link rel="prefetch" href="../../../assets/js/399.0a11557f.js"><link rel="prefetch" href="../../../assets/js/40.c0f7e504.js"><link rel="prefetch" href="../../../assets/js/400.7b2afabb.js"><link rel="prefetch" href="../../../assets/js/401.4b72ea6e.js"><link rel="prefetch" href="../../../assets/js/402.b94737bf.js"><link rel="prefetch" href="../../../assets/js/403.a838c189.js"><link rel="prefetch" href="../../../assets/js/404.9a221abc.js"><link rel="prefetch" href="../../../assets/js/405.f8f3b954.js"><link rel="prefetch" href="../../../assets/js/406.2de8752c.js"><link rel="prefetch" href="../../../assets/js/407.eb35a188.js"><link rel="prefetch" href="../../../assets/js/408.47acaedf.js"><link rel="prefetch" href="../../../assets/js/409.aa2afaca.js"><link rel="prefetch" href="../../../assets/js/41.e7e858c4.js"><link rel="prefetch" href="../../../assets/js/410.722e5009.js"><link rel="prefetch" href="../../../assets/js/411.2dbe321f.js"><link rel="prefetch" href="../../../assets/js/412.39ed2d1e.js"><link rel="prefetch" href="../../../assets/js/413.a088f973.js"><link rel="prefetch" href="../../../assets/js/414.87f8d239.js"><link rel="prefetch" href="../../../assets/js/415.f90a1394.js"><link rel="prefetch" href="../../../assets/js/416.ea4ba38d.js"><link rel="prefetch" href="../../../assets/js/417.8a1c9676.js"><link rel="prefetch" href="../../../assets/js/418.79315748.js"><link rel="prefetch" href="../../../assets/js/419.1e3ddf12.js"><link rel="prefetch" href="../../../assets/js/42.8202bb44.js"><link rel="prefetch" href="../../../assets/js/420.b6d9e0cb.js"><link rel="prefetch" href="../../../assets/js/421.0c74ef64.js"><link rel="prefetch" href="../../../assets/js/422.94c20620.js"><link rel="prefetch" href="../../../assets/js/423.fa0d6946.js"><link rel="prefetch" href="../../../assets/js/424.9036ac4f.js"><link rel="prefetch" href="../../../assets/js/425.8d8e82da.js"><link rel="prefetch" href="../../../assets/js/426.03b7da89.js"><link rel="prefetch" href="../../../assets/js/427.e3c8c690.js"><link rel="prefetch" href="../../../assets/js/428.96fdfdce.js"><link rel="prefetch" href="../../../assets/js/429.a8863be5.js"><link rel="prefetch" href="../../../assets/js/43.55e30ded.js"><link rel="prefetch" href="../../../assets/js/430.305aa090.js"><link rel="prefetch" href="../../../assets/js/431.e02d3725.js"><link rel="prefetch" href="../../../assets/js/432.83fa148f.js"><link rel="prefetch" href="../../../assets/js/433.1f01715d.js"><link rel="prefetch" href="../../../assets/js/434.f3354ec6.js"><link rel="prefetch" href="../../../assets/js/435.245644ed.js"><link rel="prefetch" href="../../../assets/js/436.3c933a14.js"><link rel="prefetch" href="../../../assets/js/437.b62325fb.js"><link rel="prefetch" href="../../../assets/js/438.8e122783.js"><link rel="prefetch" href="../../../assets/js/439.e95fa7e6.js"><link rel="prefetch" href="../../../assets/js/44.e5c90e7a.js"><link rel="prefetch" href="../../../assets/js/440.5c168090.js"><link rel="prefetch" href="../../../assets/js/441.f8835ac7.js"><link rel="prefetch" href="../../../assets/js/442.cf1de5dc.js"><link rel="prefetch" href="../../../assets/js/443.50ec8b3c.js"><link rel="prefetch" href="../../../assets/js/444.1600e74f.js"><link rel="prefetch" href="../../../assets/js/445.795f336d.js"><link rel="prefetch" href="../../../assets/js/446.1fb71b0f.js"><link rel="prefetch" href="../../../assets/js/447.0136df19.js"><link rel="prefetch" href="../../../assets/js/448.abf11aa4.js"><link rel="prefetch" href="../../../assets/js/449.4f99bee8.js"><link rel="prefetch" href="../../../assets/js/45.89368cde.js"><link rel="prefetch" href="../../../assets/js/450.55da381a.js"><link rel="prefetch" href="../../../assets/js/451.97a68e8d.js"><link rel="prefetch" href="../../../assets/js/452.9dd8c55e.js"><link rel="prefetch" href="../../../assets/js/453.3167da0d.js"><link rel="prefetch" href="../../../assets/js/454.18fad993.js"><link rel="prefetch" href="../../../assets/js/455.24cbebda.js"><link rel="prefetch" href="../../../assets/js/456.a2f19b40.js"><link rel="prefetch" href="../../../assets/js/457.27f4fb5a.js"><link rel="prefetch" href="../../../assets/js/458.29941084.js"><link rel="prefetch" href="../../../assets/js/459.b0cc6f89.js"><link rel="prefetch" href="../../../assets/js/46.1f553681.js"><link rel="prefetch" href="../../../assets/js/460.86ef4965.js"><link rel="prefetch" href="../../../assets/js/461.c0b0f695.js"><link rel="prefetch" href="../../../assets/js/462.8b86c3a9.js"><link rel="prefetch" href="../../../assets/js/463.79aadbb4.js"><link rel="prefetch" href="../../../assets/js/464.9735f749.js"><link rel="prefetch" href="../../../assets/js/465.4bdede2b.js"><link rel="prefetch" href="../../../assets/js/466.ef630311.js"><link rel="prefetch" href="../../../assets/js/467.8664e027.js"><link rel="prefetch" href="../../../assets/js/468.89b3d4ff.js"><link rel="prefetch" href="../../../assets/js/469.74e0af2c.js"><link rel="prefetch" href="../../../assets/js/47.5f78034c.js"><link rel="prefetch" href="../../../assets/js/470.9643f84e.js"><link rel="prefetch" href="../../../assets/js/471.e3a1e16a.js"><link rel="prefetch" href="../../../assets/js/472.116791e2.js"><link rel="prefetch" href="../../../assets/js/473.3bb6cb3d.js"><link rel="prefetch" href="../../../assets/js/474.1a7a0724.js"><link rel="prefetch" href="../../../assets/js/475.76c29d71.js"><link rel="prefetch" href="../../../assets/js/476.fe1fd93a.js"><link rel="prefetch" href="../../../assets/js/477.e3f203de.js"><link rel="prefetch" href="../../../assets/js/478.b34f8b1c.js"><link rel="prefetch" href="../../../assets/js/479.06d48874.js"><link rel="prefetch" href="../../../assets/js/48.0c8a5846.js"><link rel="prefetch" href="../../../assets/js/480.ffc77ad4.js"><link rel="prefetch" href="../../../assets/js/481.2475d5aa.js"><link rel="prefetch" href="../../../assets/js/482.5da370b4.js"><link rel="prefetch" href="../../../assets/js/483.2531f6c1.js"><link rel="prefetch" href="../../../assets/js/484.3f8b3ccf.js"><link rel="prefetch" href="../../../assets/js/485.fb51e0ee.js"><link rel="prefetch" href="../../../assets/js/486.881d8443.js"><link rel="prefetch" href="../../../assets/js/487.36a959cb.js"><link rel="prefetch" href="../../../assets/js/488.086e154c.js"><link rel="prefetch" href="../../../assets/js/489.2afd2906.js"><link rel="prefetch" href="../../../assets/js/49.40a46041.js"><link rel="prefetch" href="../../../assets/js/490.a517239f.js"><link rel="prefetch" href="../../../assets/js/491.c1f416dd.js"><link rel="prefetch" href="../../../assets/js/492.e36de27b.js"><link rel="prefetch" href="../../../assets/js/493.af28d057.js"><link rel="prefetch" href="../../../assets/js/494.00509c64.js"><link rel="prefetch" href="../../../assets/js/495.8ed86db6.js"><link rel="prefetch" href="../../../assets/js/496.412e9f2d.js"><link rel="prefetch" href="../../../assets/js/497.ed9f0eeb.js"><link rel="prefetch" href="../../../assets/js/498.2112afec.js"><link rel="prefetch" href="../../../assets/js/499.743df60a.js"><link rel="prefetch" href="../../../assets/js/5.6f1dbbc3.js"><link rel="prefetch" href="../../../assets/js/50.54de97a0.js"><link rel="prefetch" href="../../../assets/js/500.ab62befa.js"><link rel="prefetch" href="../../../assets/js/501.0d54ba9a.js"><link rel="prefetch" href="../../../assets/js/502.2f3bf836.js"><link rel="prefetch" href="../../../assets/js/503.c3473b0e.js"><link rel="prefetch" href="../../../assets/js/504.cb511490.js"><link rel="prefetch" href="../../../assets/js/505.a2cc8bdc.js"><link rel="prefetch" href="../../../assets/js/506.f8ea663a.js"><link rel="prefetch" href="../../../assets/js/507.9cc67e94.js"><link rel="prefetch" href="../../../assets/js/508.e08708a1.js"><link rel="prefetch" href="../../../assets/js/509.437502c3.js"><link rel="prefetch" href="../../../assets/js/51.af5d6a98.js"><link rel="prefetch" href="../../../assets/js/510.f5e8a583.js"><link rel="prefetch" href="../../../assets/js/511.6fe511a5.js"><link rel="prefetch" href="../../../assets/js/512.459d2d68.js"><link rel="prefetch" href="../../../assets/js/513.a5f4a768.js"><link rel="prefetch" href="../../../assets/js/514.5fd44c68.js"><link rel="prefetch" href="../../../assets/js/515.c046ffb4.js"><link rel="prefetch" href="../../../assets/js/516.a972f386.js"><link rel="prefetch" href="../../../assets/js/517.7c804920.js"><link rel="prefetch" href="../../../assets/js/518.ff4a2545.js"><link rel="prefetch" href="../../../assets/js/519.b1a8d590.js"><link rel="prefetch" href="../../../assets/js/52.b766b60a.js"><link rel="prefetch" href="../../../assets/js/520.2d34cada.js"><link rel="prefetch" href="../../../assets/js/521.f14247fe.js"><link rel="prefetch" href="../../../assets/js/522.38b72172.js"><link rel="prefetch" href="../../../assets/js/523.203fa4ac.js"><link rel="prefetch" href="../../../assets/js/524.236cda03.js"><link rel="prefetch" href="../../../assets/js/525.8caaad18.js"><link rel="prefetch" href="../../../assets/js/526.9a217f1e.js"><link rel="prefetch" href="../../../assets/js/527.06dc5a4f.js"><link rel="prefetch" href="../../../assets/js/528.fa884c79.js"><link rel="prefetch" href="../../../assets/js/529.d39c75ca.js"><link rel="prefetch" href="../../../assets/js/53.eae65ff7.js"><link rel="prefetch" href="../../../assets/js/530.65b18758.js"><link rel="prefetch" href="../../../assets/js/531.85af388d.js"><link rel="prefetch" href="../../../assets/js/532.9d3f253a.js"><link rel="prefetch" href="../../../assets/js/533.77c2fa0b.js"><link rel="prefetch" href="../../../assets/js/534.924b0eb2.js"><link rel="prefetch" href="../../../assets/js/535.5854a4b0.js"><link rel="prefetch" href="../../../assets/js/536.8b57b538.js"><link rel="prefetch" href="../../../assets/js/537.ccb7ba0f.js"><link rel="prefetch" href="../../../assets/js/538.b9c6c631.js"><link rel="prefetch" href="../../../assets/js/539.63164d8a.js"><link rel="prefetch" href="../../../assets/js/54.9b89b352.js"><link rel="prefetch" href="../../../assets/js/540.7e721167.js"><link rel="prefetch" href="../../../assets/js/541.0cd3f048.js"><link rel="prefetch" href="../../../assets/js/542.6c5df759.js"><link rel="prefetch" href="../../../assets/js/543.e1eebdf2.js"><link rel="prefetch" href="../../../assets/js/544.a3042061.js"><link rel="prefetch" href="../../../assets/js/545.1a468f89.js"><link rel="prefetch" href="../../../assets/js/546.892c1841.js"><link rel="prefetch" href="../../../assets/js/547.c6b5ef7e.js"><link rel="prefetch" href="../../../assets/js/548.19d884b6.js"><link rel="prefetch" href="../../../assets/js/549.7c34a773.js"><link rel="prefetch" href="../../../assets/js/55.b8a1964e.js"><link rel="prefetch" href="../../../assets/js/550.25f7d121.js"><link rel="prefetch" href="../../../assets/js/551.b9f92580.js"><link rel="prefetch" href="../../../assets/js/552.bf452147.js"><link rel="prefetch" href="../../../assets/js/553.4815ac56.js"><link rel="prefetch" href="../../../assets/js/554.c253ebb4.js"><link rel="prefetch" href="../../../assets/js/555.4dbef2bf.js"><link rel="prefetch" href="../../../assets/js/556.69f1a9f4.js"><link rel="prefetch" href="../../../assets/js/557.679e8f40.js"><link rel="prefetch" href="../../../assets/js/558.9515c7f8.js"><link rel="prefetch" href="../../../assets/js/559.eb17cff7.js"><link rel="prefetch" href="../../../assets/js/56.88fa6666.js"><link rel="prefetch" href="../../../assets/js/560.0de96a04.js"><link rel="prefetch" href="../../../assets/js/561.cdf49465.js"><link rel="prefetch" href="../../../assets/js/562.2e895986.js"><link rel="prefetch" href="../../../assets/js/563.40a31d36.js"><link rel="prefetch" href="../../../assets/js/564.08f15bf2.js"><link rel="prefetch" href="../../../assets/js/565.e7625c6a.js"><link rel="prefetch" href="../../../assets/js/566.71ee556d.js"><link rel="prefetch" href="../../../assets/js/567.7b3e4bc8.js"><link rel="prefetch" href="../../../assets/js/568.1a3c07dc.js"><link rel="prefetch" href="../../../assets/js/569.4c496338.js"><link rel="prefetch" href="../../../assets/js/57.519e80e3.js"><link rel="prefetch" href="../../../assets/js/570.d152813e.js"><link rel="prefetch" href="../../../assets/js/571.e46946bd.js"><link rel="prefetch" href="../../../assets/js/572.ae215a0d.js"><link rel="prefetch" href="../../../assets/js/573.da7b2e6c.js"><link rel="prefetch" href="../../../assets/js/574.f068c09e.js"><link rel="prefetch" href="../../../assets/js/575.f2a128db.js"><link rel="prefetch" href="../../../assets/js/576.caf5cd68.js"><link rel="prefetch" href="../../../assets/js/577.b6c04041.js"><link rel="prefetch" href="../../../assets/js/578.a7707d73.js"><link rel="prefetch" href="../../../assets/js/579.ed17d056.js"><link rel="prefetch" href="../../../assets/js/58.963c2309.js"><link rel="prefetch" href="../../../assets/js/580.88a2ce36.js"><link rel="prefetch" href="../../../assets/js/581.f99a65fa.js"><link rel="prefetch" href="../../../assets/js/582.220ce66c.js"><link rel="prefetch" href="../../../assets/js/583.5d42e8cc.js"><link rel="prefetch" href="../../../assets/js/584.0737b3ce.js"><link rel="prefetch" href="../../../assets/js/585.516b187d.js"><link rel="prefetch" href="../../../assets/js/586.420c42c4.js"><link rel="prefetch" href="../../../assets/js/587.e3deb3dd.js"><link rel="prefetch" href="../../../assets/js/588.c9bfc1e3.js"><link rel="prefetch" href="../../../assets/js/589.ee0f90f3.js"><link rel="prefetch" href="../../../assets/js/59.d9b10f83.js"><link rel="prefetch" href="../../../assets/js/590.ea859bba.js"><link rel="prefetch" href="../../../assets/js/591.4b9781fd.js"><link rel="prefetch" href="../../../assets/js/592.c9da8cab.js"><link rel="prefetch" href="../../../assets/js/6.064acd80.js"><link rel="prefetch" href="../../../assets/js/60.7e89993c.js"><link rel="prefetch" href="../../../assets/js/61.f2039f26.js"><link rel="prefetch" href="../../../assets/js/62.474d18a7.js"><link rel="prefetch" href="../../../assets/js/63.3a64d334.js"><link rel="prefetch" href="../../../assets/js/64.4a3787c2.js"><link rel="prefetch" href="../../../assets/js/65.120ee13f.js"><link rel="prefetch" href="../../../assets/js/66.e7ff207d.js"><link rel="prefetch" href="../../../assets/js/67.55a68541.js"><link rel="prefetch" href="../../../assets/js/68.33f8c7cd.js"><link rel="prefetch" href="../../../assets/js/69.69d65c56.js"><link rel="prefetch" href="../../../assets/js/7.ce2273f5.js"><link rel="prefetch" href="../../../assets/js/70.93a544a5.js"><link rel="prefetch" href="../../../assets/js/71.6eb8d306.js"><link rel="prefetch" href="../../../assets/js/72.8cbae6a9.js"><link rel="prefetch" href="../../../assets/js/73.c737f071.js"><link rel="prefetch" href="../../../assets/js/74.d0ea9fc8.js"><link rel="prefetch" href="../../../assets/js/75.2219607f.js"><link rel="prefetch" href="../../../assets/js/76.278079e2.js"><link rel="prefetch" href="../../../assets/js/77.88eadfbc.js"><link rel="prefetch" href="../../../assets/js/78.257c919e.js"><link rel="prefetch" href="../../../assets/js/79.e6f5d078.js"><link rel="prefetch" href="../../../assets/js/8.e2571ee7.js"><link rel="prefetch" href="../../../assets/js/80.40ba7856.js"><link rel="prefetch" href="../../../assets/js/81.1fab2c8f.js"><link rel="prefetch" href="../../../assets/js/82.6eaad50e.js"><link rel="prefetch" href="../../../assets/js/83.9f3e6e81.js"><link rel="prefetch" href="../../../assets/js/84.060f32c2.js"><link rel="prefetch" href="../../../assets/js/85.c069396c.js"><link rel="prefetch" href="../../../assets/js/86.ced213eb.js"><link rel="prefetch" href="../../../assets/js/87.763c4879.js"><link rel="prefetch" href="../../../assets/js/88.69fe1a6a.js"><link rel="prefetch" href="../../../assets/js/89.b87612a8.js"><link rel="prefetch" href="../../../assets/js/9.5f2fc60c.js"><link rel="prefetch" href="../../../assets/js/90.6132ccb6.js"><link rel="prefetch" href="../../../assets/js/91.27dbc3bd.js"><link rel="prefetch" href="../../../assets/js/92.88832461.js"><link rel="prefetch" href="../../../assets/js/93.0166bd69.js"><link rel="prefetch" href="../../../assets/js/94.e29b12f1.js"><link rel="prefetch" href="../../../assets/js/95.f1245827.js"><link rel="prefetch" href="../../../assets/js/96.9c45438e.js"><link rel="prefetch" href="../../../assets/js/97.e8783a06.js"><link rel="prefetch" href="../../../assets/js/98.60628afa.js"><link rel="prefetch" href="../../../assets/js/99.b3cb9d0f.js">
    <link rel="stylesheet" href="../../../assets/css/0.styles.f7de6701.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container no-sidebar"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="../../../index.html" class="home-link router-link-active"><!----> <span class="site-name">代码重工</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <!----></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><!---->  <!----> </aside> <main class="page"> <div class="theme-default-content content__default"><p></p><div class="table-of-contents"><ul><li><a href="#_1、概念">1、概念</a><ul><li><a href="#_1mysql-内优化器">①MySQL 内优化器</a></li><li><a href="#_2explain-分析">②Explain 分析</a></li></ul></li><li><a href="#_2、准备测试数据">2、准备测试数据</a></li><li><a href="#_3、id-字段">3、id 字段</a><ul><li><a href="#_1id-字段含义">①id 字段含义</a></li><li><a href="#_2举例">②举例</a></li></ul></li><li><a href="#_4、select-type-字段">4、select_type 字段</a></li><li><a href="#_5、table-字段">5、table 字段</a></li><li><a href="#_6、partitions-字段">6、partitions 字段</a></li><li><a href="#_7、type-字段-重要">7、type 字段 [重要]</a></li><li><a href="#_8、possible-keys-字段">8、possible_keys 字段</a></li><li><a href="#_9、key-字段">9、key 字段</a></li><li><a href="#_10、key-len-字段-重要">10、key_len 字段[重要]</a></li><li><a href="#_11、ref-字段">11、ref 字段</a><ul><li><a href="#_1const">①const</a></li><li><a href="#_2某个字段">②某个字段</a></li></ul></li><li><a href="#_12、rows-字段">12、rows 字段</a></li><li><a href="#_13、filtered-字段">13、filtered 字段</a></li><li><a href="#_14、extra-字段">14、extra 字段</a></li><li><a href="#_15、使用建议">15、使用建议</a></li></ul></div><p></p> <h1 id="第六章-explain-分析"><a href="#第六章-explain-分析" class="header-anchor">#</a> 第六章 Explain 分析</h1> <h2 id="_1、概念"><a href="#_1、概念" class="header-anchor">#</a> 1、概念</h2> <h3 id="_1mysql-内优化器"><a href="#_1mysql-内优化器" class="header-anchor">#</a> ①MySQL 内优化器</h3> <p>MySQL 体系结构中，包含 SQL 解析器、优化器等组件。SQL 解析器解析 SQL 之后，生成解析树。经过验证，解析树正确后，由优化器进一步优化解析树。</p> <h3 id="_2explain-分析"><a href="#_2explain-分析" class="header-anchor">#</a> ②Explain 分析</h3> <p>使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。
分析你的查询语句或是表结构的性能瓶颈。</p> <table><thead><tr><th>列名</th> <th>描述</th></tr></thead> <tbody><tr><td><code>id</code></td> <td>在一个大的查询语句中每个SELECT关键字都对应一个<code>唯一的id</code></td></tr> <tr><td><code>select_type</code></td> <td>SELECT关键字对应的那个查询的类型</td></tr> <tr><td><code>table</code></td> <td>表名</td></tr> <tr><td><code>partitions</code></td> <td>匹配的分区信息</td></tr> <tr><td><code>type</code></td> <td>针对单表的访问方法</td></tr> <tr><td><code>possible_keys</code></td> <td>可能用到的索引</td></tr> <tr><td><code>key</code></td> <td>实际上使用的索引</td></tr> <tr><td><code>key_len</code></td> <td>实际使用到的索引长度</td></tr> <tr><td><code>ref</code></td> <td>当使用索引列等值查询时，与索引列进行等值匹配的对象信息</td></tr> <tr><td><code>rows</code></td> <td>预估的需要读取的记录条数</td></tr> <tr><td><code>filtered</code></td> <td>某个表经过搜索条件过滤后剩余记录条数的百分比</td></tr> <tr><td><code>Extra</code></td> <td>一些额外的信息</td></tr></tbody></table> <h2 id="_2、准备测试数据"><a href="#_2、准备测试数据" class="header-anchor">#</a> 2、准备测试数据</h2> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">database</span> db_hr<span class="token punctuation">;</span>

<span class="token keyword">use</span> db_hr<span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1
<span class="token punctuation">(</span>
    id      <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    content <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2
<span class="token punctuation">(</span>
    id      <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    content <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t3
<span class="token punctuation">(</span>
    id      <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    content <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t4
<span class="token punctuation">(</span>
    id      <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    content <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1<span class="token punctuation">(</span>content<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span>CONCAT<span class="token punctuation">(</span><span class="token string">'t1_'</span><span class="token punctuation">,</span> FLOOR<span class="token punctuation">(</span><span class="token number">1</span> <span class="token operator">+</span> RAND<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t2<span class="token punctuation">(</span>content<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span>CONCAT<span class="token punctuation">(</span><span class="token string">'t2_'</span><span class="token punctuation">,</span> FLOOR<span class="token punctuation">(</span><span class="token number">1</span> <span class="token operator">+</span> RAND<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t3<span class="token punctuation">(</span>content<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span>CONCAT<span class="token punctuation">(</span><span class="token string">'t3_'</span><span class="token punctuation">,</span> FLOOR<span class="token punctuation">(</span><span class="token number">1</span> <span class="token operator">+</span> RAND<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t4<span class="token punctuation">(</span>content<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span>CONCAT<span class="token punctuation">(</span><span class="token string">'t4_'</span><span class="token punctuation">,</span> FLOOR<span class="token punctuation">(</span><span class="token number">1</span> <span class="token operator">+</span> RAND<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br></div></div><h2 id="_3、id-字段"><a href="#_3、id-字段" class="header-anchor">#</a> 3、id 字段</h2> <h3 id="_1id-字段含义"><a href="#_1id-字段含义" class="header-anchor">#</a> ①id 字段含义</h3> <ul><li>一个 select 对应一个 id 唯一的值</li> <li>id 值每出现一次，就代表 SQL 语句要执行一趟</li> <li>总体的 SQL 语句执行的『趟数』越少越好</li> <li>多个 id 值
<ul><li>id 值相同：按照从上到下的顺序依次执行</li> <li>id 值不同：数值大的操作先执行</li></ul></li></ul> <h3 id="_2举例"><a href="#_2举例" class="header-anchor">#</a> ②举例</h3> <h4 id="_1-id-值相同的情况"><a href="#_1-id-值相同的情况" class="header-anchor">#</a> [1]id 值相同的情况</h4> <p>SQL 本身：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> t1<span class="token punctuation">.</span>id<span class="token punctuation">,</span>t2<span class="token punctuation">.</span>id<span class="token punctuation">,</span>t3<span class="token punctuation">.</span>id<span class="token punctuation">,</span>t4<span class="token punctuation">.</span>id <span class="token keyword">from</span> t1<span class="token punctuation">,</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">,</span>t4
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>应用 Explain 分析：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> t1<span class="token punctuation">.</span>id<span class="token punctuation">,</span>t2<span class="token punctuation">.</span>id<span class="token punctuation">,</span>t3<span class="token punctuation">.</span>id<span class="token punctuation">,</span>t4<span class="token punctuation">.</span>id <span class="token keyword">from</span> t1<span class="token punctuation">,</span>t2<span class="token punctuation">,</span>t3<span class="token punctuation">,</span>t4
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>部分执行结果：</p> <p><img src="" alt="images"></p> <h4 id="_2-id-值不同的情况"><a href="#_2-id-值不同的情况" class="header-anchor">#</a> [2]id 值不同的情况</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">EXPLAIN</span>
<span class="token keyword">SELECT</span> t1<span class="token punctuation">.</span>id
<span class="token keyword">FROM</span> t1
<span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>id <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t2<span class="token punctuation">.</span>id <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>id <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t3<span class="token punctuation">.</span>id <span class="token keyword">FROM</span> t3 <span class="token keyword">WHERE</span> t3<span class="token punctuation">.</span>content <span class="token operator">=</span> <span class="token string">'t3_354'</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="" alt="images"></p> <h4 id="_3-相同的-id-值和不同的-id-值都有"><a href="#_3-相同的-id-值和不同的-id-值都有" class="header-anchor">#</a> [3]相同的 id 值和不同的 id 值都有</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">EXPLAIN</span>
<span class="token keyword">SELECT</span> t1<span class="token punctuation">.</span>id<span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword">select</span> t4<span class="token punctuation">.</span>id <span class="token keyword">from</span> t4 <span class="token keyword">where</span> t4<span class="token punctuation">.</span>id <span class="token operator">=</span> t1<span class="token punctuation">.</span>id<span class="token punctuation">)</span> id4
<span class="token keyword">FROM</span> t1<span class="token punctuation">,</span>
     t2
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="" alt="images"></p> <h4 id="_4-有子查询但是-id-值相同"><a href="#_4-有子查询但是-id-值相同" class="header-anchor">#</a> [4]有子查询但是 id 值相同</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span>
<span class="token keyword">select</span> t1<span class="token punctuation">.</span>id
<span class="token keyword">from</span> t1
<span class="token keyword">where</span> t1<span class="token punctuation">.</span>id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token keyword">select</span> t2<span class="token punctuation">.</span>id <span class="token keyword">from</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="" alt="images"></p> <p>这是因为查询优化器将子查询转换为了连接查询。</p> <h2 id="_4、select-type-字段"><a href="#_4、select-type-字段" class="header-anchor">#</a> 4、select_type 字段</h2> <p>一条 SQL 语句总体来看：其中可能会包含很多个 select 关键字。每一个 select 代表一次小的查询，而每一个 select 关键字的每一次查询都有可能是不同类型的查询。select_type 字段就是用来描述每一个 select 关键字的查询类型，意思是我们只要知道了某个小查询的<code>select_type属性</code>，就知道了这个小查询在整个大查询中<span style="color:blue;font-weight:bold;">扮演</span>了一个什么<span style="color:blue;font-weight:bold;">角色</span>。而通过查看各个小查询部分扮演的角色，我们可以了解到整体 SQL 语句的结构，从而判断当前 SQL 语句的结构是否存在问题。</p> <table><thead><tr><th>取值</th> <th>含义</th></tr></thead> <tbody><tr><td>SIMPLE</td> <td>简单的 select 查询，查询中不包含子查询或者 UNION</td></tr> <tr><td>PRIMARY</td> <td>查询中若包含任何复杂的子部分，最外层查询则被标记为 primary</td></tr> <tr><td>SUBQUERY</td> <td>在 SELECT 或 WHERE 列表中包含了子查询</td></tr> <tr><td>DEPENDENT SUBQUERY</td> <td>在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层</td></tr> <tr><td>UNCACHEABLE SUBQUREY</td> <td>表示这个 subquery 的查询要受到外部表查询的影响</td></tr> <tr><td>DERIVED</td> <td>在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)。MySQL 会递归执行这些子查询，把结果放在临时表里</td></tr> <tr><td>UNION</td> <td>这是 UNION 语句其中的一个 SQL 元素</td></tr> <tr><td>UNION RESULT</td> <td>从 UNION 表获取结果的 SELECT，也就是在 UNION 合并查询结果的基础上，不使用全部字段，选取一部分字段。</td></tr></tbody></table> <p>具体分析如下：</p> <ul><li><p><code>SIMPLE</code></p> <p>查询语句中不包含<code>UNION</code>、不包含子查询的查询都算作是<code>SIMPLE</code>类型，比方说下边这个单表查询的<code>select_type</code>的值就是<code>SIMPLE</code>：</p> <div class="language- line-numbers-mode"><pre class="language-text"><code>mysql&gt; EXPLAIN SELECT * FROM s1;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><img src="" alt="image-20210810213211336"></p> <p>当然，连接查询也算是<code>SIMPLE</code>类型，比如：</p> <div class="language- line-numbers-mode"><pre class="language-text"><code>mysql&gt; EXPLAIN SELECT * FROM s1 INNER JOIN s2;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><img src="" alt="image-20210810213232889"></p></li> <li><p><code>PRIMARY</code></p> <p>对于包含<code>UNION</code>、<code>UNION ALL</code>或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的<code>select_type</code>值就是<code>PRIMARY</code>，比方说：</p> <div class="language- line-numbers-mode"><pre class="language-text"><code>mysql&gt; EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><img src="" alt="image-20210810213252095"></p> <p>从结果中可以看到，最左边的小查询<code>SELECT * FROM s1</code>对应的是执行计划中的第一条记录，它的<code>select_type</code>值就是<code>PRIMARY</code>。</p></li> <li><p><code>UNION</code></p> <p>对于包含<code>UNION</code>或者<code>UNION ALL</code>的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的<code>select_type</code>值就是<code>UNION</code>，可以对比上一个例子的效果，这就不多举例子了。</p></li> <li><p><code>UNION RESULT</code></p> <p><code>MySQL</code>选择使用临时表来完成<code>UNION</code>查询的去重工作，针对该临时表的查询的<code>select_type</code>就是<code>UNION RESULT</code>，例子上边有。</p></li> <li><p><code>SUBQUERY</code></p> <p>如果包含子查询的查询语句不能够转为对应的<code>semi-join</code>的形式（不用管什么是 semi-join，只需要知道这是进一步优化），并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个<code>SELECT</code>关键字代表的那个查询的<code>select_type</code>就是<code>SUBQUERY</code>，比如下边这个查询：</p> <div class="language- line-numbers-mode"><pre class="language-text"><code>mysql&gt; EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><img src="" alt="image-20210810213311371"></p> <p>可以看到，外层查询的<code>select_type</code>就是<code>PRIMARY</code>，子查询的<code>select_type</code>就是<code>SUBQUERY</code>。需要大家注意的是，由于 select_type 为 SUBQUERY 的子查询会被物化（将子查询结果作为一个临时表来加快查询执行速度），所以只需要执行一遍。</p></li> <li><p><code>DEPENDENT SUBQUERY</code></p> <p>如果整体 SQL 语句执行的顺序是：</p> <ul><li>先执行外层查询</li> <li>再执行内层子查询</li> <li>然后外层查询结果中的每一条再去匹配内层子查询结果的每一条</li></ul> <p>这样，内外两层的查询结果就是<span style="color:blue;font-weight:bold;">相乘的关系</span>。相乘就有可能导致总的查询操作次数非常大。所以经过 explain 分析后，如果发现查询类型是 DEPENDENT SUBQUERY 就需要引起各位注意了——这是一个<span style="color:red;font-weight:bold;">危险</span>的信号，通常是需要修复的一个问题！</p> <p>当然，就实际工作中来说：别说 DEPENDENT SUBQUERY，就连 SUBQUERY 都不应该出现。</p></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">EXPLAIN</span>
<span class="token keyword">SELECT</span> t1<span class="token punctuation">.</span>id<span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword">select</span> t4<span class="token punctuation">.</span>id <span class="token keyword">from</span> t4 <span class="token keyword">where</span> t4<span class="token punctuation">.</span>id <span class="token operator">=</span> t1<span class="token punctuation">.</span>id<span class="token punctuation">)</span> id4
<span class="token keyword">FROM</span> t1<span class="token punctuation">,</span>
     t2<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h2 id="_5、table-字段"><a href="#_5、table-字段" class="header-anchor">#</a> 5、table 字段</h2> <p>显示当前这一步查询操作所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是别名。不论我们的查询语句有多复杂，里边儿<code>包含了多少个表</code>，到最后也是需要对每个表进行<code>单表访问</code>的，所以 MySQL 规定<strong>EXPLAIN语句输出的每条记录都对应着某个单表的访问方法</strong>，该条记录的table列代表着该表的表名。</p> <h2 id="_6、partitions-字段"><a href="#_6、partitions-字段" class="header-anchor">#</a> 6、partitions 字段</h2> <p>代表分区表中的命中情况。如果是非分区表，该项为 null。逻辑上是一个整体的数据，可以在物理层保存时，拆分成很多个分片。分片在分区中保存。数据分片的好处是：</p> <ul><li>方便在很多个不同分区之间方便的移动分片，分摊负载，弹性扩容。</li> <li>给主分片创建复制分片可以增强数据安全性。</li></ul> <p><img src="https://heavy_code_industry.gitee.io/code_heavy_industry/assets/img/img004.773696c7.png" alt="images"></p> <h2 id="_7、type-字段-重要"><a href="#_7、type-字段-重要" class="header-anchor">#</a> 7、type 字段 [重要]</h2> <p>对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。具体取值参见下表（从上到下，性能越来越好）：</p> <table><thead><tr><th>取值</th> <th>含义</th></tr></thead> <tbody><tr><td>ALL</td> <td>全表扫描</td></tr> <tr><td>index</td> <td>在索引表（聚簇索引、非聚簇索引都算）中做全表扫描</td></tr> <tr><td>range</td> <td>在一定范围内查询索引表</td></tr> <tr><td>ref</td> <td>通过普通的二级索引列与常量进行等值匹配时来查询某个表</td></tr> <tr><td>eq_ref</td> <td>在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是<code>eq_ref</code></td></tr> <tr><td>const</td> <td>根据主键或者唯一二级索引列与常数进行等值匹配</td></tr> <tr><td>system</td> <td>表仅有一行记录，这是const类型的特例，查询起来非常迅速</td></tr> <tr><td>null</td> <td>MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。</td></tr></tbody></table> <p>在上述查询方式中，从 eq_ref 开始，条件就很苛刻了，不容易达到。所以实际开发时要求，<span style="color:blue;font-weight:bold;">至少能达到 range 水平</span>，最好能达到 ref。</p> <p>下面是可以参考的例子：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment"># 创建数据库表</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> t_emp
<span class="token punctuation">(</span>
    emp_id     <span class="token keyword">int</span> <span class="token keyword">auto_increment</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    emp_name   <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    emp_salary <span class="token keyword">double</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    dept_id    <span class="token keyword">int</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">create</span> <span class="token keyword">table</span> t_dept
<span class="token punctuation">(</span>
    dept_id   <span class="token keyword">int</span> <span class="token keyword">auto_increment</span> <span class="token keyword">primary</span> <span class="token keyword">key</span><span class="token punctuation">,</span>
    dept_name <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># emp_id 主键索引</span>
<span class="token comment"># emp_name 唯一索引</span>
<span class="token keyword">create</span> <span class="token keyword">unique</span> <span class="token keyword">index</span> idx_emp_name_unique <span class="token keyword">on</span> t_emp <span class="token punctuation">(</span>emp_name<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment"># dept_name 普通索引</span>
<span class="token keyword">create</span> <span class="token keyword">index</span> idx_dept_name <span class="token keyword">on</span> t_dept <span class="token punctuation">(</span>dept_name<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># 情况一：type 的值是 all</span>
<span class="token comment"># 原因：由于没有用到任何索引，所以执行全表扫描</span>
<span class="token keyword">explain</span>
<span class="token keyword">select</span> emp_salary
<span class="token keyword">from</span> t_emp<span class="token punctuation">;</span>

<span class="token comment"># 情况二：type 的值是 index</span>
<span class="token comment"># 原因：查询的是建立了索引的字段，而且没有指定 where 条件。</span>
<span class="token comment"># 在执行查询时扫描索引的整个 B+Tree</span>
<span class="token keyword">explain</span>
<span class="token keyword">select</span> emp_id
<span class="token keyword">from</span> t_emp<span class="token punctuation">;</span>

<span class="token keyword">explain</span>
<span class="token keyword">select</span> emp_name
<span class="token keyword">from</span> t_emp<span class="token punctuation">;</span>

<span class="token keyword">explain</span>
<span class="token keyword">select</span> dept_name
<span class="token keyword">from</span> t_dept<span class="token punctuation">;</span>

<span class="token comment"># 情况三：type 的值是 range</span>
<span class="token comment"># 原因：在一定范围内访问索引 B+Tree</span>

<span class="token comment"># emp_salary 普通索引</span>
<span class="token keyword">create</span> <span class="token keyword">index</span> idx_emp_salary <span class="token keyword">on</span> t_emp <span class="token punctuation">(</span>emp_salary<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">explain</span>
<span class="token keyword">select</span> emp_id<span class="token punctuation">,</span> emp_name
<span class="token keyword">from</span> t_emp
<span class="token keyword">where</span> emp_salary <span class="token operator">between</span> <span class="token number">1000</span> <span class="token operator">and</span> <span class="token number">5000</span><span class="token punctuation">;</span>

<span class="token comment"># 情况四：type 的值是 ref</span>
<span class="token comment"># 原因：通过普通的二级索引列与常量进行等值匹配时来查询</span>
<span class="token keyword">explain</span>
<span class="token keyword">select</span> dept_name
<span class="token keyword">from</span> t_dept
<span class="token keyword">where</span> dept_name <span class="token operator">=</span> <span class="token string">'研发部'</span><span class="token punctuation">;</span>

<span class="token comment"># 情况五：对 t_dept 表的查询中，type 的值是 eq_ref</span>
<span class="token comment"># 原因：在进行关联查询时，被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问</span>
<span class="token keyword">explain</span>
<span class="token keyword">select</span> emp_id<span class="token punctuation">,</span> emp_name<span class="token punctuation">,</span> emp_salary
<span class="token keyword">from</span> t_emp e
         <span class="token keyword">left</span> <span class="token keyword">join</span> t_dept td <span class="token keyword">on</span> e<span class="token punctuation">.</span>dept_id <span class="token operator">=</span> td<span class="token punctuation">.</span>dept_id<span class="token punctuation">;</span>

<span class="token comment"># type 的值是 const</span>
<span class="token comment"># 原因：使用常量值查询一个唯一索引，返回唯一一条记录</span>
<span class="token keyword">explain</span>
<span class="token keyword">select</span> emp_id<span class="token punctuation">,</span> emp_name<span class="token punctuation">,</span> emp_salary
<span class="token keyword">from</span> t_emp
<span class="token keyword">where</span> emp_name <span class="token operator">=</span> <span class="token string">'aaa'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br><span class="line-number">52</span><br><span class="line-number">53</span><br><span class="line-number">54</span><br><span class="line-number">55</span><br><span class="line-number">56</span><br><span class="line-number">57</span><br><span class="line-number">58</span><br><span class="line-number">59</span><br><span class="line-number">60</span><br><span class="line-number">61</span><br><span class="line-number">62</span><br><span class="line-number">63</span><br><span class="line-number">64</span><br><span class="line-number">65</span><br><span class="line-number">66</span><br><span class="line-number">67</span><br><span class="line-number">68</span><br><span class="line-number">69</span><br><span class="line-number">70</span><br><span class="line-number">71</span><br><span class="line-number">72</span><br><span class="line-number">73</span><br></div></div><h2 id="_8、possible-keys-字段"><a href="#_8、possible-keys-字段" class="header-anchor">#</a> 8、possible_keys 字段</h2> <p>在查询中有可能会用到的索引列。如果没有任何索引显示 null。</p> <h2 id="_9、key-字段"><a href="#_9、key-字段" class="header-anchor">#</a> 9、key 字段</h2> <p>key 列显示 MySQL 实际决定使用的键（索引），包含在 possible_keys 中。</p> <h2 id="_10、key-len-字段-重要"><a href="#_10、key-len-字段-重要" class="header-anchor">#</a> 10、key_len 字段[重要]</h2> <p>key_len 表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要，值越大索引的效果越好——因为值越大说明索引被利用的越充分。</p> <p>字节数计算方式：</p> <ul><li>索引对应字段类型：
<ul><li>数值类型：最终结果就是数值类型字段宽度本身</li> <li>字符串类型：查看字符集类型
<ul><li>UTF-8：需要给字段长度 × 3</li> <li>GBK：需要给字段长度 × 2</li></ul></li> <li>如果是 varchar 这样的变长字符串类型：再 + 2</li> <li>如果是允许为空的字段：再 + 1</li></ul></li></ul> <p>举例：customer_name 字段声明的类型是 varchar(200)，允许为空。</p> <p>200 × 3 + 2 + 1 = 603</p> <p>举例：</p> <blockquote><div class="language- line-numbers-mode"><pre class="language-text"><code># 下面分析结果的 key_len 字段的值是 310，我们来看看是怎么算出来的
# 先看 emp_name
# emp_name 是字符串类型，它的字段宽度是 100，字符集是 UTF-8 需要乘 3，是定长字段不需要 +2，允许为空需要 +1，所以：100×3+1 = 301
# emp_salary 是数值类型，本身占 8 个字节，允许为空需要 + 1，所以：8 + 1 = 9
# 总和：301 + 9 = 310
explain select emp_name,emp_salary from t_emp
where emp_name = '李四' or emp_salary = 1000;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div></blockquote> <h2 id="_11、ref-字段"><a href="#_11、ref-字段" class="header-anchor">#</a> 11、ref 字段</h2> <p>表示查询条件中，我们的索引列和谁去比较，是常量还是另一张表的字段。</p> <h3 id="_1const"><a href="#_1const" class="header-anchor">#</a> ①const</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> emp_id<span class="token punctuation">,</span>emp_name<span class="token punctuation">,</span>emp_salary <span class="token keyword">from</span> t_emp <span class="token keyword">where</span> emp_id<span class="token operator">=</span><span class="token number">5</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><img src="" alt="images"></p> <h3 id="_2某个字段"><a href="#_2某个字段" class="header-anchor">#</a> ②某个字段</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">explain</span>
<span class="token keyword">select</span> emp_id<span class="token punctuation">,</span> emp_name<span class="token punctuation">,</span> emp_salary
<span class="token keyword">from</span> t_emp e
         <span class="token keyword">left</span> <span class="token keyword">join</span> t_dept d <span class="token keyword">on</span> d<span class="token punctuation">.</span>dept_id <span class="token operator">=</span> e<span class="token punctuation">.</span>dept_id
<span class="token keyword">where</span> emp_id <span class="token operator">=</span> d<span class="token punctuation">.</span>dept_id<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p><img src="" alt="images"></p> <h2 id="_12、rows-字段"><a href="#_12、rows-字段" class="header-anchor">#</a> 12、rows 字段</h2> <p><span style="color:blue;font-weight:bold;">估算出结果集行数</span>，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。通常来说越小越好。</p> <h2 id="_13、filtered-字段"><a href="#_13、filtered-字段" class="header-anchor">#</a> 13、filtered 字段</h2> <p>通过存储引擎从硬盘加载数据到服务层时，受限于内存空间，有可能只能加载一部分数据。filtered 字段显示的值是：已加载数据 / 全部数据 的百分比。只是不显示百分号。</p> <p><img src="https://heavy_code_industry.gitee.io/code_heavy_industry/assets/img/img005.6634afb5.png" alt="images"></p> <h2 id="_14、extra-字段"><a href="#_14、extra-字段" class="header-anchor">#</a> 14、extra 字段</h2> <p>顾名思义，<code>Extra</code> 列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来<code>更准确的理解MySQL到底将如何执行给定的查询语句</code>。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑比较重要的额外信息介绍给大家。</p> <p>下面蓝色字体需要适当留意一下：</p> <table><thead><tr><th>取值</th> <th>含义</th></tr></thead> <tbody><tr><td><span style="color:blue;font-weight:bold;">using where</span></td> <td>不用读取表中所有信息，仅通过索引就可以获取所需数据。<br>言外之意是 select 查询的字段都带有索引。<br>不管 select 查询多少个字段，这些字段都在索引中。</td></tr> <tr><td><span style="color:blue;font-weight:bold;">Using temporary</span></td> <td>表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询</td></tr> <tr><td><span style="color:red;font-weight:bold;">Using filesort</span></td> <td>当语句中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”<br>这里的文件指的是保存在硬盘上的文件。<br>之所以会用到硬盘，是因为如果查询的数据量太大，内存空间不够，需要在硬盘上完成排序。<br>如果确实是很大数据量在硬盘执行排序操作，那么速度会非常慢。</td></tr> <tr><td>Using join buffer</td> <td>buffer 指缓冲区，该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。<br>举例来说：where t_name like &quot;%xxx%&quot;，这个条件中的 t_name 字段没有加索引</td></tr> <tr><td>Impossible where</td> <td>where 子句中指定的条件查询不到数据的情况</td></tr> <tr><td>Select tables optimized away</td> <td>这个值表示目前的查询使用了索引，然后经过优化器优化之后，最终执行的是一个聚合函数，从而让最终的查询结果只返回一行</td></tr> <tr><td>No tables used</td> <td>查询语句中使用 from dual 或不含任何 from 子句</td></tr></tbody></table> <h2 id="_15、使用建议"><a href="#_15、使用建议" class="header-anchor">#</a> 15、使用建议</h2> <ul><li>EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况</li> <li>EXPLAIN 不考虑各种 Cache</li> <li>EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作</li> <li>部分统计信息是估算的，并非精确值</li> <li>EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划</li></ul> <p><a href="../chapter05/index.html">上一章</a> <a href="../index.html">回目录</a> <a href="../chapter07/index.html">下一章</a></p></div> <footer class="page-edit"><!----> <!----></footer> <!----> </main></div><div class="global-ui"></div></div>
    <script src="../../../assets/js/app.972bd0f0.js" defer></script><script src="../../../assets/js/4.904e76b5.js" defer></script><script src="../../../assets/js/25.a5f2f737.js" defer></script>
  </body>

<!-- Mirrored from heavy_code_industry.gitee.io/code_heavy_industry/pro018-MySQL-high/lecture/chapter06/index.html by HTTrack Website Copier/3.x [XR&CO'2014], Tue, 10 May 2022 05:13:08 GMT -->
</html>
